package cn.jasonone.dao;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;

import cn.jasonone.bean.Book;
import cn.jasonone.util.DBUtil;
import cn.jasonone.vo.PageInfo;

public class BookDao {

	private Book parseBook(ResultSet rs) throws SQLException {
		Book book = new Book();
		book.setId(rs.getInt("id"));
		book.setInventory(rs.getInt("inventory"));
		book.setStatus(rs.getInt("status"));
		book.setPrice(rs.getDouble("price"));
		book.setName(rs.getString("name"));
		book.setPublisher(rs.getString("publisher"));
		book.setCreateTime(rs.getTimestamp("create_time"));
		book.setUpdateTime(rs.getTimestamp("update_time"));
		return book;
	}

	public Book findById(Integer id) {
		String sql = "select * from book where id=? ";

		try (Connection conn = DBUtil.getConnection()) {
			try (PreparedStatement ps = conn.prepareStatement(sql)) {
				ps.setInt(1, id);
				try (ResultSet rs = ps.executeQuery()) {
					if (rs.next()) {
						return this.parseBook(rs);
					}
				}
			}
		} catch (SQLException e) {
			e.printStackTrace();
		}
		return null;
	}

	public PageInfo<Book> findAll(int page,int pageSize,Book book) {
		PageInfo<Book> pageInfo = new PageInfo<>(page, pageSize);
		List<Book> books = new ArrayList<>();
		String sql = "select * from book where 1=1 ";
		List<Object> params = new ArrayList<>();
		if (book != null) {
			if (book.getId() != null) {
				sql += " and id = ?";
				params.add(book.getId());
			}
			if (book.getPrice() != null) {
				sql += " and price = ?";
				params.add(book.getPrice());
			}
			if (book.getName() != null && !book.getName().isEmpty()) {
				sql += " and name like concat('%',?,'%')";
				params.add(book.getName());
			}
			if (book.getPublisher() != null && !book.getPublisher().isEmpty()) {
				sql += " and publisher like concat('%',?,'%')";
				params.add(book.getPublisher());
			}
		}
		
		
		
		try (Connection conn = DBUtil.getConnection()) {
			try (PreparedStatement ps = conn.prepareStatement("select count(1) from ("+sql+") t")) {
				for (int i = 0; i < params.size(); i++) {
					ps.setObject(i + 1, params.get(i));
				}
				try (ResultSet rs = ps.executeQuery()) {
					if (rs.next()) {
						pageInfo.setTotal(rs.getInt(1));
					}
				}
			}
			params.add((page-1)*pageSize);
			params.add(pageSize);
			try (PreparedStatement ps = conn.prepareStatement(sql+" limit ?,?")) {
				for (int i = 0; i < params.size(); i++) {
					ps.setObject(i + 1, params.get(i));
				}
				try (ResultSet rs = ps.executeQuery()) {
					while (rs.next()) {
						books.add(this.parseBook(rs));
					}
				}
			}
		} catch (SQLException e) {
			e.printStackTrace();
		}
		pageInfo.setList(books);
		return pageInfo;
	}

	public boolean deleteById(Integer id) {
		String sql = "delete from book where id = ?";
		try (Connection conn = DBUtil.getConnection()) {
			try (PreparedStatement ps = conn.prepareStatement(sql)) {
				ps.setInt(1, id);
				return ps.executeUpdate() > 0;
			}
		} catch (SQLException e) {
			e.printStackTrace();
		}
		return false;
	}

	public boolean addBook(Book book) {
		String sql = "insert into book(name,price,publisher,inventory,create_time,status) values(?,?,?,?,now(),?)";
		try (Connection conn = DBUtil.getConnection()) {
			try (PreparedStatement ps = conn.prepareStatement(sql)) {
				ps.setString(1, book.getName());
				ps.setDouble(2, book.getPrice());
				ps.setString(3, book.getPublisher());
				ps.setInt(4, book.getInventory());
				ps.setInt(5, book.getStatus());
				return ps.executeUpdate() > 0;
			}
		} catch (SQLException e) {
			e.printStackTrace();
		}
		return false;
	}

	public boolean updateBook(Book book) {
		String sql = "update book set ";
		List<Object> args = new ArrayList<>();
		if (book != null) {
			if (book.getName() != null && !book.getName().isEmpty()) {
				sql += " name = ? ,";
				args.add(book.getName());
			}
			if (book.getPublisher() != null && !book.getPublisher().isEmpty()) {
				sql += " publisher = ? ,";
				args.add(book.getPublisher());
			}
			if (book.getPrice() != null) {
				sql += " price = ? ,";
				args.add(book.getPrice());
			}
			if (book.getInventory() != null) {
				sql += " inventory = ? ,";
				args.add(book.getInventory());
			}
			if (book.getStatus() != null) {
				sql += " status = ? ,";
				args.add(book.getStatus());
			}
			if (sql.endsWith(",")) {
				sql = sql.substring(0, sql.length() - 1);
				if (book.getId() != null) {
					sql += " where id = ?";
					args.add(book.getId());
				}
			} else {
				return false;
			}
		} else {
			return false;
		}

		try (Connection conn = DBUtil.getConnection()) {
			try (PreparedStatement ps = conn.prepareStatement(sql)) {
				for (int i = 0; i < args.size(); i++) {
					ps.setObject(i + 1, args.get(i));
				}
				return ps.executeUpdate() > 0;
			}
		} catch (SQLException e) {
			e.printStackTrace();
		}
		return false;
	}
}
